﻿--假设刘备取款6000，(添加check约束，设置账户余额必须>=0)，要求：使用事务实现，修改余额和添加取款记录两步操作使用事务
--账户余额>=0约束
alter table BankCard add constraint CK_Money check(CardMoney >= 0)

declare @myError int = 0
declare @getMoney float = 6000
declare @carID varchar(10)
declare @name  varchar(10)
declare @Phone varchar(10)
set @name = '刘备'

select @carID = (select b.CardNo from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name)
select @Phone = (select a.AccountPhone from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name)
--开始事务
begin transaction A
update BankCard set CardMoney -= @getMoney where CardNo = @carID
set @myError += @@ERROR
insert into CardExchange values(@carID,0,@getMoney,getdate())
set @myError += @@ERROR
--事务提交
if(@myError = 0)
begin
	commit transaction A
end
else 
begin
	rollback transaction B
end
go

--2.刘备向张飞转账1000元，(添加check约束，设置账户余额必须>=0)
alter table BankCard add constraint CK_Money check(CardMoney >= 0)
declare @myError int = 0
declare @getMoney float = 1000
declare @carID varchar(10)
declare @carID1 varchar(10)
declare @name  varchar(10)
declare @name1  varchar(10)
declare @Phone varchar(10)
declare @Phone1 varchar(10)
set @name = '刘备'
set @name1 = '张飞'
select @carID = (select b.CardNo from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name)
select @Phone = (select a.AccountPhone from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name)
select @carID1 = (select b.CardNo from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name1)
select @Phone1 = (select a.AccountPhone from AccountInfo a inner join BankCard b on a.AccountId =b.AccountId where RealName = @name1)

begin transaction A
update BankCard set CardMoney -= @getMoney where CardNo = @carID
set @myError += @@ERROR
update BankCard set CardMoney += @getMoney where CardNo = @carID1
set @myError += @@ERROR
insert into CardTransfer values(@carID,@carID1,@getMoney,getdate())
set @myError += @@ERROR

if(@myError = 0)
begin
	commit transaction A
end
else 
begin
	rollback transaction B
end


--列转行
CREATE TABLE grades1 (
  id int NOT NULL ,
  name varchar(20) DEFAULT NULL,
  subject varchar(20) DEFAULT NULL,
  score int DEFAULT NULL,

)

INSERT INTO grades1 VALUES (1, 'lyy', '英语', 100);
INSERT INTO grades1 VALUES (2, 'lyy', '数学', 98);
INSERT INTO grades1 VALUES (3, 'lyy', '语文', 97);
INSERT INTO grades1 VALUES (4, 'gss', '英语', 89);
INSERT INTO grades1 VALUES (5, 'gss', '数学', 88);
INSERT INTO grades1 VALUES (6, 'gss', '语文', 87);
INSERT INTO grades1 VALUES (7, 'liming', '英语', 79);
INSERT INTO grades1 VALUES (8, 'liming', '数学', 77);
INSERT INTO grades1 VALUES (9, 'liming', '语文', 75);

select * from grades1

select name,
max(case subject when '英语' then score else 0 end)英语,
max(case subject when '数学' then score else 0 end)数学,
max(case subject when '语文' then score else 0 end)语文 
from grades1 
group by name

select * from grades1 
pivot
(
max(score)
for subject in([英语],[数学],[语文])
) as a
\ No newline at end of file